/*******************************************
 * File: database.cpp
 * Describe: 数据库处理
 * Author: Xiangfu DING
 * Time: 2024-03-08
*******************************************/
#include "database.h"
#include <QCoreApplication>
#include <QDebug>
#include <QDir>
#include <QJsonDocument>
#include <QJsonArray>
#include <QJsonObject>
#include <QSqlError>
#include <QSqlRecord>
#include <QtConcurrent/QtConcurrent>

DataBase *database;
DataBase::DataBase(QObject* parent)
    : QObject { parent } {
    database = this;
}

DataBase::~DataBase()
{
    if (m_db.isOpen()) {
        m_db.close();
    }
}

const bool DataBase::addDataBase(const QString &dbName, const QString &connectName)
{
#ifndef QT_NO_DEBUG
    qDebug() << Q_FUNC_INFO << __LINE__ << dbName;
#endif
    const QString path = getDataBasePath();
    QDir dir(path);
    if (!dir.exists()) {
        if (!dir.mkpath(path)) {
#ifndef QT_NO_DEBUG
            qDebug() << "创建数据库目录失败!";
#endif
            return false;
        }
    }
    m_db = QSqlDatabase::addDatabase("QSQLITE", connectName);
    m_db.setDatabaseName(path + "/" + dbName);

    // 设置数据库编码为 UTF-8
    m_db.setConnectOptions("QSQLITE_ENABLE_SHARED_CACHE = 1;QSQLITE_ENABLE_REGEXP");
    m_db.setConnectOptions("QSQLITE_ENABLE_REGEXP = 1");
    m_db.setConnectOptions("QSQLITE_ENABLE_UPDATE_DELETE_LIMIT = 1");
    m_db.setConnectOptions("QSQLITE_ENABLE_COLUMN_METADATA = 1");
    m_db.setConnectOptions("QSQLITE_OPEN_CREATE = 1");

    if (!m_db.open()) {
#ifndef QT_NO_DEBUG
        qDebug() << "打开数据库失败!";
#endif
        return false;
    }

    return true;
}

//const bool DataBase::addFerryDataBase(const QString &dbName, const QString &connectName)
//{
//#ifndef QT_NO_DEBUG
//    qDebug() << Q_FUNC_INFO << __LINE__ << dbName;
//#endif
//    const QString path = getDataBasePath();
//    QDir dir(path);
//    if (!dir.exists()) {
//        if (!dir.mkpath(path)) {
//#ifndef QT_NO_DEBUG
//            qDebug() << "创建数据库目录失败!";
//#endif
//            return false;
//        }
//    }
//    m_db = QSqlDatabase::addDatabase("SQLITECIPHER", connectName);
//    m_db.setDatabaseName(path + "/" + dbName);

//    m_db.setPassword("123456");
//    //m_db.setConnectOptions("QSQLITE_USE_CIPHER=sqlcipher;SQLCIPHER_LEGACY=1; SQLCIPHER_LEGACY_PAGE_SIZE=4096; QSQLITE_CREATE_KEY");
//    m_db.setConnectOptions("QSQLITE_USE_CIPHER=sqlcipher;SQLCIPHER_LEGACY=1; SQLCIPHER_LEGACY_PAGE_SIZE=4096;");

//    if (!m_db.open()) {
//#ifndef QT_NO_DEBUG
//        qDebug() << "Can not open connection: " << m_db.lastError().driverText();
//        qDebug() << "打开数据库失败!";
//#endif
//        m_db.close();
//        return false;
//    }

//    return true;
//}

const bool DataBase::checkTableExists(const QString &tableName)
{
    if(!m_db.isOpen()) {
        return false;
    }
    QSqlQuery query(m_db);
    query.exec(QString("select count(*) from sqlite_master where type='table' and name='%1'").arg(tableName));
    if(query.next()) {
        if(query.value(0).toInt() == 0) {
            return false;
        }
    }
    return true;
}

const bool DataBase::createTable(const QString &tableName, const QString &columns)
{
#ifndef QT_NO_DEBUG
    qDebug() << Q_FUNC_INFO << __LINE__ << tableName;
#endif
    if(!m_db.isOpen()) {
        return false;
    }
    QSqlQuery query(m_db);
    if (query.exec(QString("CREATE TABLE %1(%2);").arg(tableName).arg(columns+", id INTEGER PRIMARY KEY AUTOINCREMENT"))) {
        return true;
    } else {
        return false;
    }
}

const bool DataBase::createFerryTable(const QString &tableName, const QString &columns)
{
#ifndef QT_NO_DEBUG
    qDebug() << Q_FUNC_INFO << __LINE__ << tableName;
#endif
    if(!m_db.isOpen()) {
        return false;
    }
    QSqlQuery query(m_db);
    if (query.exec(QString("CREATE TABLE %1(%2);").arg(tableName).arg(columns))) {
        return true;
    } else {
        return false;
    }
}

const bool DataBase::removeTable(const QString &tableName)
{
#ifndef QT_NO_DEBUG
    qDebug() << Q_FUNC_INFO << __LINE__ << tableName;
#endif
    if(!m_db.isOpen()) {
        return false;
    }
    QSqlQuery query(m_db);
    if(query.exec(QString("DROP TABLE %1;").arg(tableName))) {
        return true;
    } else {
        return false;
    }
}

const bool DataBase::addFerryRecord(const QString &tableName, const QVariantMap &records)
{
       QVariantMap map = records;
#ifndef QT_NO_DEBUG
       qDebug() << Q_FUNC_INFO << __LINE__ << tableName << map;
#endif
       if(!m_db.isOpen()) {
           return false;
       }
       if(!checkTableExists(tableName)) {
           return false;
       }

       QString sql = "INSERT INTO " + tableName + " (";
       QStringList fieldList, placeholderList;
       foreach(const QString &field, map.keys()) {
           fieldList.append(field);
           placeholderList.append(":" + field);
       }
       sql += fieldList.join(", ") + ") VALUES (" + placeholderList.join(", ") + ")";

       QSqlQuery query(m_db);
       query.prepare(sql);

       foreach(const QString &field, map.keys()) {
           const QByteArray ba = map.value(field).toByteArray();
           query.bindValue(":" + field, map.value(field).toByteArray());
       }

       if (query.exec()) {
           return true;
       } else {
#ifndef QT_NO_DEBUG
           qDebug() << "Error executing SQL query:" << query.lastError().text();
#endif
           return false;
       }
}


const bool DataBase::addRecord(const QString &tableName, const QVariantMap &records)
{
    QJsonObject jsonObject = QJsonObject::fromVariantMap(records);
#ifndef QT_NO_DEBUG
    qDebug() << Q_FUNC_INFO << __LINE__ << tableName << jsonObject;
#endif
    if(!m_db.isOpen()) {
        return false;
    }
    if(!checkTableExists(tableName)) {
        return false;
    }
    QSqlQuery query(m_db);
    QString sqlStr = QString("INSERT INTO %1 (").arg(tableName);
    {
        QList<QString> fieldList, valueList;
        foreach (const QString& field, jsonObject.keys()) {
            fieldList << field;
            valueList << "\'" + jsonObject.value(field).toString() +"\'" ;
        }
        sqlStr += QString("%1)VALUES(%2);")
                      .arg(fieldList.join(QLatin1String(",")))
                      .arg(valueList.join(QLatin1String(",")));
    }

    // 执行SQL语句
        if (query.exec(sqlStr)) {
            return true;
        } else {
#ifndef QT_NO_DEBUG
            qDebug() << "Error executing SQL query:" << query.lastError().text();
#endif
            return false;
        }
    //return query.exec(sqlStr);
}

const int DataBase::queryRecordCount(const QString &tableName)
{
    if(!m_db.isOpen()) {
        return 0;
    }
    if(!checkTableExists(tableName)) {
        return 0;
    }
    QSqlQuery query(m_db);
    QString sqlStr = QString("SELECT COUNT(*) FROM %1;").arg(tableName);
    if(query.exec(sqlStr)) {
        if (query.next()) {
            return query.value(0).toInt();
        }
    } else {
        return 0;
    }
}

const QVariant DataBase::queryPagingRecord(const QString &tableName, int page, int count)
{
    QJsonArray jsonArray;

    if(!m_db.isOpen()) {
        return QVariant::fromValue(jsonArray);
    }
    if(!checkTableExists(tableName)) {
        return QVariant::fromValue(jsonArray);
    }
    int offset = (page - 1) * count;
    QSqlQuery query(m_db);
    query.prepare("SELECT * FROM " + tableName + " ORDER BY id DESC LIMIT :count OFFSET :offset ");
    query.bindValue(":count", count);
    query.bindValue(":offset", offset);
    if (query.exec()) {
        QSqlRecord record = query.record();
        int columnCount = record.count();
        while (query.next()) {
            QJsonObject jsonObject;
            for(int i = 0;i < columnCount;i++) {
                jsonObject.insert(record.fieldName(i), query.value(i).toString());
            }
            jsonArray.append(jsonObject);
        }
    }

    return QVariant::fromValue(jsonArray);
}

const QVariant DataBase::queryAllRecord(const QString &tableName)
{
    QJsonArray jsonArray;

    if(!m_db.isOpen()) {
        return QVariant::fromValue(jsonArray);
    }
    if(!checkTableExists(tableName)) {
        return QVariant::fromValue(jsonArray);
    }
    QSqlQuery query(m_db);
    QString sqlStr = QString("SELECT * FROM %1 ORDER BY id DESC").arg(tableName);
    if(query.exec(sqlStr)) {
        QSqlRecord record = query.record();
        int columnCount = record.count();
        while (query.next()) {
            QJsonObject jsonObject;
            for(int i = 0;i < columnCount;i++) {
                jsonObject.insert(record.fieldName(i), query.value(i).toString());
            }
            jsonArray.append(jsonObject);
        }
    }
    return QVariant::fromValue(jsonArray);
}

QVariantList DataBase::queryFerryAllRecord(const QString &tableName)
{
    QVariantList resultList;

    if (!m_db.isOpen()) {
        return resultList;
    }
    if (!checkTableExists(tableName)) {
        return resultList;
    }

    QSqlQuery query(m_db);
    QString sqlStr = QString("SELECT * FROM %1 ORDER BY id DESC").arg(tableName);
    if (query.exec(sqlStr)) {
        QSqlRecord record = query.record();
        int columnCount = record.count();

        while (query.next()) {
            QVariantMap recordMap;
            for (int i = 0; i < columnCount; ++i) {
                QString fieldName = record.fieldName(i);
                QVariant value = query.value(i);
                QByteArray byteArray = value.toByteArray();
                recordMap[fieldName] = QVariant::fromValue(byteArray); // Store QByteArray as QVariant
            }
            resultList.append(QVariant::fromValue(recordMap)); // Store QVariantMap in QVariantList
        }
    }

    return resultList;
}

const bool DataBase::removeAllRecord(const QString &tableName)
{
#ifndef QT_NO_DEBUG
    qDebug() << Q_FUNC_INFO << __LINE__ << tableName;
#endif
    if(!m_db.isOpen()) {
        return false;
    }
    QSqlQuery query(m_db);
    if(query.exec(QString("delete from %1;").arg(tableName))) {
        return true;
    } else {
        return false;
    }
}

const QString DataBase::getDataBasePath()
{
    return QCoreApplication::applicationDirPath()+"/db";
}

const QString DataBase::getDataBaseSize()
{
    QString databasePath = m_db.databaseName();

    QFileInfo fileInfo(databasePath);
    if (!fileInfo.exists()) {
        qDebug() << "File doesn't exist!";
        return "0 Bytes";
    }

    qint64 fileSize = fileInfo.size();

    QString fileSizeString;
    if (fileSize < 1024) {
        fileSizeString = QString::number(fileSize) + " Bytes";
    } else if (fileSize < 1024 * 1024) {
        fileSizeString = QString::number(fileSize / 1024) + " KB";
    } else if (fileSize < 1024 * 1024 * 1024) {
        fileSizeString = QString::number(fileSize / (1024 * 1024)) + " MB";
    } else {
        fileSizeString = QString::number(fileSize / (1024 * 1024 * 1024)) + " GB";
    }

    return fileSizeString;
}
